﻿using System;
using System.Collections.Generic;
using System.Globalization;

using MySql.Data.MySqlClient;



namespace StagingAreaETL
{
    class StagingArea2OperationalDataStore
    {
        //StudyStatusKEY
        const string STUDYING = "riadne denné štúdium";
        const string INTERRUPTED = "prerušené štúdium";
        const string GRADUATED = "úspešne absolvované štúdium";
        const string CANCELED = "štúdium ukončené vylúčením";

        private readonly Dictionary<string, StudentDIM> students;      //Key is AIS_ID
        private readonly Dictionary<int, ExamFCT> examFacts;
        private readonly Dictionary<int, UserAccountDIM> userAccounts;
        private readonly Dictionary<int, LibraryAccountDIM> libAccounts;

        private readonly Random random;
        private readonly TermManagement termsMng;
        private readonly SimpleTransport transport;

        //Lowest TermKEY (supposed to be assigned to older term)
        private int minTermKey;


        internal StagingArea2OperationalDataStore()
        {
            students = new Dictionary<string, StudentDIM>();
            examFacts = new Dictionary<int, ExamFCT>();
            userAccounts = new Dictionary<int, UserAccountDIM>();
            libAccounts = new Dictionary<int, LibraryAccountDIM>();
            random = new Random(123);
            termsMng = new TermManagement();
            transport = new SimpleTransport();
        }


        /// <summary>
        /// Clear all ODS tables, so they can be used by this ETL
        /// </summary>
        internal void Initialize()
        {
            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "TRUNCATE TABLE Book;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE BorrowingFacts;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Library;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Faculty;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE LibraryAccount;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE FieldOfStudy;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE AggregateRatings;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE UserAccount;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Student;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE StudyStatus;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Term;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE SessionFacts;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Time;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE ExamFacts;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Computer;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Room;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE Course;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE ExamType;";
            command.ExecuteNonQuery();
            Console.WriteLine("Staging area data cleared.\n==========================");
            connection.Close();

            connection = DBHelper.getSAConnection();
            command = connection.CreateCommand();

            command.CommandText = "SELECT min(TermKey) FROM Term;";
            MySqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
                minTermKey = reader.GetInt32(0);
            else throw new InvalidOperationException("Staging area: Unable to get lowest term");

            connection.Close();
        }


        internal void Extract()
        {
            downloadStudents1();
            Console.WriteLine("Students from the table SA.Student downloaded...");
            downloadStudents2();
            Console.WriteLine("Students from the table SA.lib_student downloaded...");
            downloadExamFacts();
            Console.WriteLine("Data from the table SA.examfacts downloaded...");
            downloadUserAccounts();
            Console.WriteLine("Data from the table SA.UserAccount downloaded...");
            downloadLibraryAccounts();
            Console.WriteLine("Data from the table SA.lib_libraryaccount downloaded...\n");
            termsMng.DownloadTerms();
        }

        /// <summary>
        /// First take STUDENT intersection from Study dept. and HW+SW
        /// </summary>
        private void downloadStudents1()
        {
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "select * from Student";         
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string ais = reader.GetString("AIS_ID");
                if(ais=="-1") continue;     //no AIS ID, no need to continue

                int natKey1 = reader.GetInt32("StudentKey");
                int? fos = reader.IsDBNull(3) ? (int?)null : reader.GetInt32("FieldOfStudyKey");
                string name = reader.GetString("FirstName");
                string surname = reader.GetString("LastName");
                int? grade = reader.IsDBNull(7) ? (int?)null : reader.GetInt32("Grade");

                students.Add(ais, new StudentDIM(natKey1, ais, fos, name, surname, grade));
            }

            connection.Close();
        }

        private void downloadStudents2()
        {
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "select * from lib_student";
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int natKey2 = reader.GetInt32("studentKEY");
                int ais = reader.GetInt32("ais_id");

                //AIS ID is what connects the two STUDENT tables together.
                //After this point, every StudentDIM holds StudentKEYs (natural keys) from both.
                if(students.ContainsKey(ais.ToString()))
                    students[ais.ToString()].NaturalKEY_2 = natKey2;
            }

            connection.Close();
        }

        private void downloadExamFacts()
        {
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "select * from examfacts";
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int studKey = reader.GetInt32("StudentKey");
                int? roomKey = reader.IsDBNull(1) ? (int?)null : reader.GetInt32("RoomKey");
                int? examTypeKey = reader.IsDBNull(2) ? (int?)null : reader.GetInt32("ExamTypeKey");
                int? courseKey = reader.IsDBNull(3) ? (int?)null : reader.GetInt32("CourseKey");
                string timeKey = reader.GetString("TimeKey");
                timeKey = timeKey.Replace(' ', '-').Replace(':', '-');
                int? termKey = reader.IsDBNull(5) ? (int?)null : reader.GetInt32("TermKey");
                string asmnt = reader.GetString("Assessment");

                examFacts.Add(studKey, new ExamFCT(studKey, roomKey, examTypeKey, courseKey, timeKey, termKey, asmnt));
            }

            connection.Close();
        }

        private void downloadUserAccounts()
        {
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "select * from useraccount";
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int accountKey = reader.GetInt32("UserAccountKey");
                int studKey = reader.GetInt32("StudentKey");
                string login = reader.GetString("Username");
                string domain = reader.GetString("Domain");
                string creationTimeKey = reader.GetString("CreationTimeKey");
                creationTimeKey = creationTimeKey.Replace(' ', '-').Replace(':', '-');
                string locked = reader.GetString("Locked");         //binary ('0' or '1')
                string deleted = reader.GetString("Deleted");       //binary

                userAccounts.Add(studKey, new UserAccountDIM(studKey, accountKey, login, domain, creationTimeKey, locked=="1", deleted=="1"));
            }

            connection.Close();
        }

        private void downloadLibraryAccounts()
        {
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "select * from lib_libraryaccount";
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int accountKey = reader.GetInt32("libraryAccountKEY");
                int studKey = reader.GetInt32("studentKEY");
                string creationTimeKey = reader.IsDBNull(2) ? null
                                                            : reader.GetString("creationTimeKEY").Replace(' ', '-').Replace(':', '-');
                bool active = reader.GetBoolean("active");

                libAccounts.Add(studKey, new LibraryAccountDIM(accountKey, creationTimeKey, active));
            }

            connection.Close();
        }



        internal void Load()
        {
            DBHelper.CarryOverTableData("faculty", "Faculty");   //TODO: let it be like this so far
            Console.WriteLine("Faculties saved to ODS");
            transport.CarryOverTime();
            Console.WriteLine("Times saved to ODS");
            DBHelper.CarryOverTableData("lib_library", "Library");
            Console.WriteLine("Libraries saved to ODS");
            transport.CarryOverBorrowings();
            Console.WriteLine("Borrowings' facts saved to ODS");
            DBHelper.CarryOverTableData("lib_book", "Book");
            Console.WriteLine("Books saved to ODS");
            DBHelper.CarryOverTableData("FieldOfStudy", "FieldOfStudy");
            Console.WriteLine("Fields of study saved to ODS");
            DBHelper.CarryOverTableData("Course", "Course");
            Console.WriteLine("Courses saved to ODS");
            DBHelper.CarryOverTableData("Term", "Term");
            Console.WriteLine("Terms saved to ODS");
            DBHelper.CarryOverTableData("ExamType", "ExamType");
            Console.WriteLine("Exam types saved to ODS");
            DBHelper.CarryOverTableData("Room", "Room");
            Console.WriteLine("Rooms saved to ODS");
            DBHelper.CarryOverTableData("Computer", "Computer");
            Console.WriteLine("Computers saved to ODS");
            transport.CarryOverSessions();
            Console.WriteLine("Sessions saved to ODS");
            saveStudents();
            Console.WriteLine("Students with their aggregated ratings saved to ODS");
            updateStudentDependencies();
            saveExams();
            Console.WriteLine("Exams saved to ODS");
            saveUserAccounts();
            Console.WriteLine("Computer user accounts saved to ODS");
            saveLibraryAccounts();
            correctLibraryAccounts();
            Console.Write("Library accounts saved to ODS");
            updateLibraryAccounts();
            Console.WriteLine(". And updated for active students");
        }


       
        private void saveStudents()
        {
            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (StudentDIM student in students.Values)
            {
                DateTime ratingPeriodEnd;
                int statusKey = saveStudyStatus(out ratingPeriodEnd);

                command.CommandText = "insert into Student(AIS_ID, FieldOfStudyKey, StudyStatusKey, FirstName, LastName, Grade) " +
                                      " values('" + student.AIS_id + "', " + DBHelper.toString(student.FieldOfStudyKEY) + ", " + 
                                      statusKey + ", '" + DBHelper.toString(student.FirstName) + "', '" +
                                      DBHelper.toString(student.LastName) + "', " + DBHelper.toString(student.Grade) + ");";
                command.ExecuteNonQuery();
                int id = DBHelper.getLastID(connection);
                student.SurrogateKEY = id;

                saveAggegateRatings(ratingPeriodEnd, student.SurrogateKEY);
            }

            connection.Close();    
        }

        private void saveExams()
        {
            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (int sKey in examFacts.Keys)
            {
                ExamFCT exam = examFacts[sKey];
                command.CommandText = "insert into ExamFacts values(" + exam.StudentSurrogateKEY + ", " + DBHelper.toString(exam.RoomKEY) +
                                      ", " + DBHelper.toString(exam.ExamTypeKEY) + ", " + DBHelper.toString(exam.CourseKEY) + ", '" +
                                      DBHelper.toString(exam.TimeKEY) + "', " + DBHelper.toString(exam.TermKEY) + ", '" + 
                                      DBHelper.toString(exam.Assessment) + "');";

                command.ExecuteNonQuery();
            }

            connection.Close();
        }

        private void saveUserAccounts()
        {
            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (int sKey in userAccounts.Keys)
            {
                UserAccountDIM account = userAccounts[sKey];
                command.CommandText = "insert into UserAccount values(" + account.NaturalKEY + ", " + account.StudentSurrogateKEY +
                                      ", '" + account.Username + "', '" + DBHelper.toString(account.Domain) + "', '" +
                                      DBHelper.toString(account.CreationTimeKEY) + "', " + DBHelper.toString(account.Locked) + ", " +
                                      DBHelper.toString(account.Deleted) + ");";

                command.ExecuteNonQuery();
            }

            connection.Close();
        }

        private void saveLibraryAccounts()
        {
            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (int sKey in libAccounts.Keys)
            {
                LibraryAccountDIM account = libAccounts[sKey];
                if(account.StudentSurrogateKEY==-1)     //not a student
                    continue;
                command.CommandText = "insert into LibraryAccount values(" + account.NaturalKEY + ", " + account.StudentSurrogateKEY +
                                      ", " + (account.CreationTimeKEY!=null ? "'"+DBHelper.toString(account.CreationTimeKEY)+"'" : "NULL" ) +
                                      ", " + DBHelper.toString(account.Active) + ");";

                command.ExecuteNonQuery();
            }

            connection.Close();
        }


        private void correctLibraryAccounts()
        {
            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "update ODS.LibraryAccount set active=1 " +
                                  " where LibraryAccountKey in (162 ,163 ,171 ,192 ,234 ,376 ,421 ,670 ,742 ,750);";
            command.ExecuteScalar();
            connection.Close();
        }


        /// <summary>
        /// Update library accounts already on ODS because this wasn't possible in previous ETL processes.
        /// The 'Active' field is set to TRUE and CreationTimeKEY to valid date-stamp only for actively studying persons.
        /// </summary>
        private void updateLibraryAccounts()
        {
            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "update LibraryAccount la " +
                                  " inner join Student st on (la.StudentKey=st.StudentKey) " +
                                  " inner join StudyStatus ss on (st.StudyStatusKey=ss.StudyStatusKey) " +
                                  " set la.CreationTimeKey=ss.StartTimeKey, la.Active=1 " +
                                  " where ss.name like 'riadne de%'; ";
            command.ExecuteNonQuery();
            connection.Close();
        }



        /// <summary>
        /// Save new StudyStatus of a student.
        /// </summary>
        /// <param name="aggPeriodEnd">The end date for aggregated ratings valid for a student in respective state.</param>
        /// <returns>StudyStatusKEY of saved status. Meant to be used in ODS.STUDENT table as a foreign key.</returns>
        private int saveStudyStatus(out DateTime aggPeriodEnd)
        {
            const double p_stud = 0.61;
            const double p_interr = 0.08;
            const double p_grad = 0.12;
//            const double p_cancl = 0.19;

            string statusName = CANCELED;
            DateTime startDate = new DateTime(2010, 9, 1);      //as I wrote: we
            DateTime? endDate = new DateTime(2011, 6, 30);      //are desperate

            double rnd = random.NextDouble();
            if(rnd < p_stud)
            {
                statusName = STUDYING;
                aggPeriodEnd = endDate.Value;
            }
            else if(rnd < p_stud+p_interr)
            {
                statusName = INTERRUPTED;
                startDate.AddYears(random.NextDouble()>0.5 ? -1 : -2);
                endDate = null;
                aggPeriodEnd = startDate;
            }
            else if(rnd < p_stud+p_interr+p_grad)
            {
                statusName = GRADUATED;
                startDate = new DateTime(2010, 6, 30);
                endDate = null;
                aggPeriodEnd = startDate;
            }
            else    //canceled study (fired from school)
            {
                startDate.AddYears(random.NextDouble() > 0.5 ? 0 : -1);
                endDate = null;
                aggPeriodEnd = startDate;
            }


            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            TimeDIM startTime = new TimeDIM(startDate);
            startTime.SaveToDB();
            TimeDIM endTime = null;
            if(endDate.HasValue)
            {
                endTime = new TimeDIM(endDate.Value);
                endTime.SaveToDB();
            }
            command.CommandText = "insert into StudyStatus(Name, StartTimeKEY, EndTimeKEY) values('" + statusName + "', '" +
                                  startTime.TimeKEY + "', " + (endTime!=null ? "'"+endTime.TimeKEY+"'" : "NULL") + ");";
            command.ExecuteNonQuery();
            int id = DBHelper.getLastID(connection);

            connection.Close();

            return id;
        }



        /// <summary>
        /// Evaluate and insert to database the ratings of a student.
        /// </summary>
        /// <param name="periodEnd"></param>
        /// <param name="studentKey">ID of a student to be rated</param>
        private void saveAggegateRatings(DateTime periodEnd, int studentKey)
        {
            int endTermKey = termsMng.getTerm(periodEnd).NaturalKEY;
            int startTermKey;
            do
            {
                int rnd = random.Next(1, 6);
                startTermKey = endTermKey - rnd;
            } while (startTermKey < minTermKey);

            //The 'median' rating point from which new ratings can't differ much
            double median = random.Next(1, 5);

            List<RatingsAGG> ratings = new List<RatingsAGG>();
            for(int termKey1 = startTermKey; termKey1 <= endTermKey; termKey1++)
            {
                for(int termKey2=endTermKey; termKey2>=termKey1; termKey2--)
                {
                    bool toAdd = random.NextDouble() < 0.5;     //FALSE means that offset will be subtracted from median
                    double rating;
                    double offset = random.NextDouble();
                    if (toAdd)
                    {
                        rating = (median + offset) <= 4.0 ? median + offset
                                                          : median - offset;
                    }
                    else
                    {
                        rating = (median - offset) >= 1.0 ? median - offset
                                                          : median + offset;
                    }

                    ratings.Add(new RatingsAGG(studentKey, termKey1, termKey2, rating));
                }
            }


            MySqlConnection connection = DBHelper.getODSConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (RatingsAGG ra in ratings)
            {
                command.CommandText = "insert into AggregateRatings values(" + ra.StudentKEY + ", " + ra.StartTermKEY + ", " +
                                      ra.EndTermKEY + ", " + ra.SummaryRating.ToString(CultureInfo.InvariantCulture) + ");";
                command.ExecuteNonQuery();
            }

            connection.Close();
            Console.WriteLine("> Aggregated ratings for student with KEY " + studentKey + " saved");
        }



        /// <summary>
        /// Supposed to be called after Students have been saved and got new primary keys. According to them foreign
        /// keys in referencing tables are updated.
        /// </summary>
        private void updateStudentDependencies()
        {
            foreach (StudentDIM student in students.Values)
            {
                if(examFacts.ContainsKey(student.NaturalKEY_1))     //we're not interested in students that don't have exams
                    examFacts[student.NaturalKEY_1].StudentSurrogateKEY = student.SurrogateKEY;
                if(userAccounts.ContainsKey(student.NaturalKEY_1))  //as well for their computer accounts
                    userAccounts[student.NaturalKEY_1].StudentSurrogateKEY = student.SurrogateKEY;
                if(libAccounts.ContainsKey(student.NaturalKEY_2))   //and library accounts (this should happen rarely)
                    libAccounts[student.NaturalKEY_2].StudentSurrogateKEY = student.SurrogateKEY;
            }
        }
    }
}
